Skip Headers

Oracle® Database PL/SQL User's Guide and Reference 10g Release 2 (10.2) Part Number B14261-01 |
Home | Book List | Contents | Index | Master Index | Feedback

Previous | Next
| View PDF

# Collection Methods

A collection method is a built-in function or procedure that operates on collections and is called using dot notation. You can use the methods `EXISTS`, `COUNT`, `LIMIT`, `FIRST`, `LAST`, `PRIOR`, `NEXT`, `EXTEND`, `TRIM`, and `DELETE` to manage collections whose size is unknown or varies.

`EXISTS`, `COUNT`, `LIMIT`, `FIRST`, `LAST`, `PRIOR`, and `NEXT` are functions that check the properties of a collection or individual collection elements. `EXTEND`, `TRIM`, and `DELETE` are procedures that modify a collection.

`EXISTS`, `PRIOR`, `NEXT`, `TRIM`, `EXTEND`, and `DELETE` take integer parameters. `EXISTS`, `PRIOR`, `NEXT`, and `DELETE` can also take `VARCHAR2` parameters for associative arrays with string keys. `EXTEND` and `TRIM` cannot be used with index-by tables.

For more information, see "Using Collection Methods".

Syntax

collection call method ::=

Description of the illustration collection_method_call.gif

Keyword and Parameter Description

collection_name

An associative array, nested table, or varray previously declared within the current scope.

COUNT

`R`eturns the number of elements that a collection currently contains, which is useful because the current size of a collection is not always known. You can use `COUNT` wherever an integer expression is allowed. For varrays, `COUNT` always equals `LAST`. For nested tables, normally, `COUNT` equals `LAST`. But, if you delete elements from the middle of a nested table, `COUNT` is smaller than `LAST`.

DELETE

This procedure has three forms. `DELETE` removes all elements from a collection. `DELETE(n)` removes the `n`th element from an associative array or nested table. If `n` is null, `DELETE(n)` does nothing. `DELETE(m,n)` removes all elements in the range `m..n` from an associative array or nested table. If `m` is larger than `n` or if `m` or `n` is null, `DELETE(m,n)` does nothing.

EXISTS

`EXISTS(n)` returns `TRUE` if the `n`th element in a collection exists. Otherwise, `EXISTS(n)` returns `FALSE`. Mainly, you use `EXISTS` with `DELETE` to maintain sparse nested tables. You can also use `EXISTS` to avoid raising an exception when you reference a nonexistent element. When passed an out-of-range subscript, `EXISTS` returns `FALSE` instead of raising `SUBSCRIPT_OUTSIDE_LIMIT`.

EXTEND

This procedure has three forms. `EXTEND` appends one null element to a collection. `EXTEND(n)` appends `n` null elements to a collection. `EXTEND(n,i)` appends `n` copies of the `i`th element to a collection. `EXTEND` operates on the internal size of a collection. If `EXTEND` encounters deleted elements, it includes them in its tally. You cannot use `EXTEND` with associative arrays.

FIRST, LAST

`FIRST` and `LAST` return the first and last (smallest and largest) subscript values in a collection. The subscript values are usually integers, but can also be strings for associative arrays. If the collection is empty, `FIRST` and `LAST` return `NULL`. If the collection contains only one element, `FIRST` and `LAST` return the same subscript value. For varrays, `FIRST` always returns 1 and `LAST` always equals `COUNT`. For nested tables, normally, `LAST` equals `COUNT`. But, if you delete elements from the middle of a nested table, `LAST` is larger than `COUNT`.

index

An expression that must return (or convert implicitly to) an integer in most cases, or a string for an associative array declared with string keys.

LIMIT

For nested tables, which have no maximum size, `LIMIT` returns `NULL`. For varrays, `LIMIT` returns the maximum number of elements that a varray can contain (which you must specify in its type definition).

NEXT, PRIOR

`PRIOR(n)` returns the subscript that precedes index `n` in a collection. `NEXT(n)` returns the subscript that succeeds index `n`. If `n` has no predecessor, `PRIOR(n)` returns `NULL`. Likewise, if `n` has no successor, `NEXT(n)` returns `NULL`.

TRIM

This procedure has two forms. `TRIM` removes one element from the end of a collection. `TRIM(n)` removes `n` elements from the end of a collection. If `n` is greater than `COUNT`, `TRIM(n)` raises `SUBSCRIPT_BEYOND_COUNT`. You cannot use `TRIM` with index-by tables.`TRIM` operates on the internal size of a collection. If `TRIM` encounters deleted elements, it includes them in its tally.

Usage Notes

You cannot use collection methods in a SQL statement. If you try, you get a compilation error.

Only `EXISTS` can be applied to atomically null collections. If you apply another method to such collections, PL/SQL raises `COLLECTION_IS_NULL`.

If the collection elements have sequential subscripts, you can use `collection.FIRST` .. `collection.LAST` in a `FOR` loop to iterate through all the elements. You can use `PRIOR` or `NEXT` to traverse collections indexed by any series of subscripts. For example, you can use `PRIOR` or `NEXT` to traverse a nested table from which some elements have been deleted, or an associative array where the subscripts are string values.

`EXTEND` operates on the internal size of a collection, which includes deleted elements. You cannot use `EXTEND` to initialize an atomically null collection. Also, if you impose the `NOT` `NULL` constraint on a `TABLE` or `VARRAY` type, you cannot apply the first two forms of `EXTEND` to collections of that type.

If an element to be deleted does not exist, `DELETE` simply skips it; no exception is raised. Varrays are dense, so you cannot delete their individual elements. Because PL/SQL keeps placeholders for deleted elements, you can replace a deleted element by assigning it a new value. However, PL/SQL does not keep placeholders for trimmed elements.

The amount of memory allocated to a nested table can increase or decrease dynamically. As you delete elements, memory is freed page by page. If you delete the entire table, all the memory is freed.

In general, do not depend on the interaction between `TRIM` and `DELETE`. It is better to treat nested tables like fixed-size arrays and use only `DELETE`, or to treat them like stacks and use only `TRIM` and `EXTEND`.

Within a subprogram, a collection parameter assumes the properties of the argument bound to it. You can apply methods `FIRST`, `LAST`, `COUNT`, and so on to such parameters. For varray parameters, the value of `LIMIT` is always derived from the parameter type definition, regardless of the parameter mode.

Examples

For examples, see the following:

Example 5-28, "Checking Whether a Collection Element EXISTS"
Example 5-29, "Counting Collection Elements With COUNT"
Example 5-30, "Checking the Maximum Size of a Collection With LIMIT"
Example 5-31, "Using FIRST and LAST With a Collection"
Example 5-32, "Using PRIOR and NEXT to Access Collection Elements"
Example 5-34, "Using EXTEND to Increase the Size of a Collection"
Example 5-35, "Using TRIM to Decrease the Size of a Collection"
Example 5-37, "Using the DELETE Method on a Collection"
Example 13-1, "Declaring and Assigning Values to Variables"

Related Topics

"Collection Definition"